import logging
import os
import sys
from datetime import datetime

# 导入数据库连接模块
sys.path.append(os.path.join(os.path.dirname(__file__), '..', '..'))
from DB.DB import DB

# 配置日志
logger = logging.getLogger(__name__)


def save_salesman_customer_relation(data):
    """
    保存业务员和客户的绑定关系
    
    参数:
    data (dict): 包含业务员和客户信息的字典，必须包含以下字段:
        - salesman_name: 业务员名称
        - customer_code: 客户编码
        - customer_name: 客户名称
    
    返回:
    dict: 包含操作结果的字典
        - success: 布尔值，表示操作是否成功
        - message: 字符串，操作结果描述
    """
    try:
        # 验证必要参数
        if not data:
            return {'success': False, 'message': '数据不能为空'}

        salesman_name = data.get('salesman_name')
        customer_code = data.get('customer_code')
        customer_name = data.get('customer_name')

        if not salesman_name:
            return {'success': False, 'message': '业务员名称不能为空'}

        if not customer_code:
            return {'success': False, 'message': '客户编码不能为空'}

        # 在客户编号前添加DKH前缀
        if not customer_code.startswith('DKH'):
            customer_code = f'DKH{customer_code}'

        # 记录操作日志
        logger.info(
            f'准备保存业务员客户关系 - 业务员: {salesman_name}, 客户编码: {customer_code}, 客户名称: {customer_name}')

        # 初始化数据库连接
        db = DB()

        # 检查关系是否已存在
        check_sql = """SELECT id FROM dkh_salesman_customer_relation 
                     WHERE salesman_name = %s AND customer_code = %s"""
        check_result = db.query(check_sql, (salesman_name, customer_code))

        if check_result:
            # 关系已存在，执行更新
            update_sql = """UPDATE dkh_salesman_customer_relation 
                          SET customer_name = %s, update_time = NOW() 
                          WHERE salesman_name = %s AND customer_code = %s"""
            db.execute(update_sql, (customer_name, salesman_name, customer_code))
            logger.info(f'更新大客户业务员客户关系成功 - 业务员: {salesman_name}, 客户编码: {customer_code}')
            return {'success': True, 'message': '大客户业务员客户关系更新成功'}
        else:
            # 关系不存在，执行插入
            insert_sql = """INSERT INTO dkh_salesman_customer_relation 
                          (salesman_name, customer_code, customer_name) 
                          VALUES (%s, %s, %s)"""
            db.execute(insert_sql, (salesman_name, customer_code, customer_name))
            logger.info(f'新增业务员客户关系成功 - 业务员: {salesman_name}, 客户编码: {customer_code}')
            return {'success': True, 'message': '业务员客户关系保存成功'}

    except Exception as e:
        error_msg = f'保存业务员客户关系时发生错误: {str(e)}'
        logger.error(error_msg, exc_info=True)
        return {'success': False, 'message': error_msg}


def get_customer_by_salesman(salesman_name):
    """
    根据业务员名称获取其负责的所有客户
    
    参数:
    salesman_name (str): 业务员名称
    
    返回:
    list: 客户信息列表，每个客户包含客户编码和客户名称
    """
    try:
        if not salesman_name:
            return []

        # 初始化数据库连接
        db = DB()

        # 查询业务员负责的客户
        query_sql = """SELECT customer_code, customer_name, create_time 
                     FROM dkh_salesman_customer_relation 
                     WHERE salesman_name = %s 
                     ORDER BY create_time DESC"""
        result = db.query(query_sql, (salesman_name,))

        # 格式化结果
        customers = []
        for row in result:
            customers.append({
                'customer_code': row['customer_code'],
                'customer_name': row['customer_name'],
                'bind_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S') if row['create_time'] else ''
            })

        return customers

    except Exception as e:
        logger.error(f'获取业务员客户列表时发生错误: {str(e)}', exc_info=True)
        return []


def get_salesman_by_customer(customer_code):
    """
    根据客户编码获取负责该客户的业务员
    
    参数:
    customer_code (str): 客户编码
    
    返回:
    dict: 业务员信息，包含业务员名称和绑定时间
    """
    try:
        if not customer_code:
            return None

        # 在客户编号前添加DKH前缀（如果没有）
        if not customer_code.startswith('DKH'):
            customer_code_with_prefix = f'DKH{customer_code}'
        else:
            customer_code_with_prefix = customer_code

        # 初始化数据库连接
        db = DB()

        # 查询负责该客户的业务员
        query_sql = """SELECT salesman_name, create_time 
                     FROM dkh_salesman_customer_relation 
                     WHERE customer_code = %s 
                     LIMIT 1"""
        result = db.query(query_sql, (customer_code_with_prefix,))

        # 如果使用带前缀的客户编码没有找到结果，尝试使用原始客户编码
        if not result and customer_code != customer_code_with_prefix:
            logger.info(
                f'使用带前缀的客户编码 {customer_code_with_prefix} 未找到业务员，尝试使用原始编码 {customer_code}')
            result = db.query(query_sql, (customer_code,))

        if result:
            row = result[0]
            return {
                'salesman_name': row['salesman_name'],
                'bind_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S') if row['create_time'] else ''
            }

        return None

    except Exception as e:
        logger.error(f'获取客户业务员信息时发生错误: {str(e)}', exc_info=True)
        return None


def save_salesman_customer_relation_md(data):
    """
    保存业务员和客户的绑定关系（包含推荐人信息）
    
    参数:
    data (dict): 包含业务员和客户信息的字典，必须包含以下字段:
        - salesman_name: 业务员名称
        - customer_code: 客户编码
        - customer_name: 客户名称
        - recommend_name: 推荐人名称（可选）
        - recommend_customer_code: 推荐人编码（可选）
    
    返回:
    dict: 包含操作结果的字典
        - success: 布尔值，表示操作是否成功
        - message: 字符串，操作结果描述
    """
    try:
        # 验证必要参数
        if not data:
            return {'success': False, 'message': '数据不能为空'}

        salesman_name = data.get('salesman_name')
        customer_code = data.get('customer_code')
        customer_name = data.get('customer_name')
        recommend_name = data.get('recommend_name')
        recommend_customer_code = data.get('recommend_customer_code')

        # 处理推荐人名称，如果为空或null则设为None
        if recommend_name == 'null' or not recommend_name or recommend_name.strip() == '':
            recommend_name = None

        if not salesman_name:
            return {'success': False, 'message': '业务员名称不能为空'}

        if not customer_code:
            return {'success': False, 'message': '客户编码不能为空'}

        # 在客户编号前添加DKH前缀
        if not customer_code.startswith('DKH'):
            customer_code = f'DKH{customer_code}'

        # 在推荐人编码前添加DKH前缀（如果有且不为空）
        if recommend_customer_code and recommend_customer_code != 'null' and recommend_customer_code.strip() and not recommend_customer_code.startswith(
                'DKH'):
            recommend_customer_code = f'DKH{recommend_customer_code}'
        elif recommend_customer_code == 'null' or not recommend_customer_code or recommend_customer_code.strip() == '':
            recommend_customer_code = None

        # 记录操作日志
        logger.info(
            f'准备保存业务员客户关系(MD) - 业务员: {salesman_name}, 客户编码: {customer_code}, 客户名称: {customer_name}, 推荐人: {recommend_name}, 推荐人编码: {recommend_customer_code}')

        # 初始化数据库连接
        db = DB()

        # 检查关系是否已存在
        check_sql = """SELECT id FROM dkh_salesman_customer_relation_md 
                     WHERE salesman_name = %s AND customer_code = %s"""
        check_result = db.query(check_sql, (salesman_name, customer_code))

        if check_result:
            # 关系已存在，执行更新
            update_sql = """UPDATE dkh_salesman_customer_relation_md 
                          SET customer_name = %s, recommend_name = %s, recommend_customer_code = %s, update_time = NOW() 
                          WHERE salesman_name = %s AND customer_code = %s"""
            db.execute(update_sql,
                       (customer_name, recommend_name, recommend_customer_code, salesman_name, customer_code))
            logger.info(f'更新大客户业务员客户关系成功(MD) - 业务员: {salesman_name}, 客户编码: {customer_code}')
            return {'success': True, 'message': '大客户业务员客户关系更新成功'}
        else:
            # 关系不存在，执行插入
            insert_sql = """INSERT INTO dkh_salesman_customer_relation_md 
                          (salesman_name, customer_code, customer_name, recommend_name, recommend_customer_code) 
                          VALUES (%s, %s, %s, %s, %s)"""
            db.execute(insert_sql,
                       (salesman_name, customer_code, customer_name, recommend_name, recommend_customer_code))
            logger.info(f'新增业务员客户关系成功(MD) - 业务员: {salesman_name}, 客户编码: {customer_code}')
            return {'success': True, 'message': '业务员客户关系保存成功'}

    except Exception as e:
        error_msg = f'保存业务员客户关系时发生错误(MD): {str(e)}'
        logger.error(error_msg, exc_info=True)
        return {'success': False, 'message': error_msg}


def get_customer_by_salesman_md(salesman_name):
    """
    根据业务员名称获取其负责的所有客户（包含推荐人信息）
    
    参数:
    salesman_name (str): 业务员名称
    
    返回:
    list: 客户信息列表，每个客户包含客户编码、客户名称和推荐人信息
    """
    try:
        if not salesman_name:
            return []

        # 初始化数据库连接
        db = DB()

        # 查询业务员负责的客户
        query_sql = """SELECT customer_code, customer_name, recommend_name, recommend_customer_code, create_time 
                     FROM dkh_salesman_customer_relation_md 
                     WHERE salesman_name = %s 
                     ORDER BY create_time DESC"""
        result = db.query(query_sql, (salesman_name,))

        # 格式化结果
        customers = []
        for row in result:
            customers.append({
                'customer_code': row['customer_code'],
                'customer_name': row['customer_name'],
                'recommend_name': row['recommend_name'],
                'recommend_customer_code': row['recommend_customer_code'],
                'bind_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S') if row['create_time'] else ''
            })

        return customers

    except Exception as e:
        logger.error(f'获取业务员客户列表时发生错误(MD): {str(e)}', exc_info=True)
        return []


def get_salesman_by_customer_md(customer_code):
    """
    根据客户编码获取负责该客户的业务员（包含推荐人信息）
    
    参数:
    customer_code (str): 客户编码
    
    返回:
    dict: 业务员信息，包含业务员名称、推荐人信息和绑定时间
    """
    try:
        if not customer_code:
            return None

        # 在客户编号前添加DKH前缀（如果没有）
        if not customer_code.startswith('DKH'):
            customer_code_with_prefix = f'DKH{customer_code}'
        else:
            customer_code_with_prefix = customer_code

        # 初始化数据库连接
        db = DB()

        # 查询负责该客户的业务员
        query_sql = """SELECT salesman_name, recommend_name, recommend_customer_code, create_time 
                     FROM dkh_salesman_customer_relation_md 
                     WHERE customer_code = %s 
                     LIMIT 1"""
        result = db.query(query_sql, (customer_code_with_prefix,))

        # 如果使用带前缀的客户编码没有找到结果，尝试使用原始客户编码
        if not result and customer_code != customer_code_with_prefix:
            logger.info(
                f'使用带前缀的客户编码 {customer_code_with_prefix} 未找到业务员，尝试使用原始编码 {customer_code}')
            result = db.query(query_sql, (customer_code,))

        if result:
            row = result[0]
            return {
                'salesman_name': row['salesman_name'],
                'recommend_name': row['recommend_name'],
                'recommend_customer_code': row['recommend_customer_code'],
                'bind_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S') if row['create_time'] else ''
            }

        return None

    except Exception as e:
        logger.error(f'获取客户业务员信息时发生错误(MD): {str(e)}', exc_info=True)
        return None


def get_customers_by_recommend_md(recommend_name):
    """
    根据推荐人名称获取其推荐的所有客户
    
    参数:
    recommend_name (str): 推荐人名称
    
    返回:
    list: 客户信息列表，每个客户包含客户编码、客户名称和业务员信息
    """
    try:
        if not recommend_name:
            return []

        # 初始化数据库连接
        db = DB()

        # 查询推荐人推荐的客户
        query_sql = """SELECT customer_code, customer_name, salesman_name, recommend_customer_code, create_time 
                     FROM dkh_salesman_customer_relation_md 
                     WHERE recommend_name = %s 
                     ORDER BY create_time DESC"""
        result = db.query(query_sql, (recommend_name,))

        # 格式化结果
        customers = []
        for row in result:
            customers.append({
                'customer_code': row['customer_code'],
                'customer_name': row['customer_name'],
                'salesman_name': row['salesman_name'],
                'recommend_customer_code': row['recommend_customer_code'],
                'bind_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S') if row['create_time'] else ''
            })

        return customers

    except Exception as e:
        logger.error(f'获取推荐人客户列表时发生错误(MD): {str(e)}', exc_info=True)
        return []
